import os.path
import openpyxl
import datetime
import time
import pymysql
import logging
import sys

timeCur = datetime.datetime.now()
fileNamePath = 'E:\\日常工作\\工单汇总\\2022\\'
logFile = fileNamePath + 'AutoImportExcel.log'
# 创建logger对象
logger = logging.getLogger('test_logger')

# 设置日志等级
logger.setLevel(logging.DEBUG)

# 追加写入文件a ，设置utf-8编码防止中文写入乱码
test_log = logging.FileHandler('test.log', 'a', encoding='utf-8')

# 向文件输出的日志级别
test_log.setLevel(logging.DEBUG)

# 向文件输出的日志信息格式
formatter = logging.Formatter('%(asctime)s - %(filename)s - line:%(lineno)d - %(levelname)s - %(message)s -%(process)s')

test_log.setFormatter(formatter)

# 加载文件到logger对象中
logger.addHandler(test_log)

log = logging.getLogger()
stdout_handler = logging.StreamHandler(sys.stdout)
log.addHandler(stdout_handler)


def get_excel_file_name(file_parth_excel, num_month):
    file_name_cur = file_parth_excel + '{}月份全部数据.xlsx'.format(num_month)
    return file_name_cur


def get_work_sheet(file_name):
    book_source = openpyxl.load_workbook(file_name)
    sheets_list = book_source.sheetnames
    # logging.debug('{}:{}-{}'.format(file_name, sheets_list, sheets_list[0]))
    sheet_source = book_source[sheets_list[0]]
    # logging.debug('{}----max_row:{}'.format(file_name, sheet_source.max_row))
    # logging.debug('{}----max_column:{}'.format(file_name, sheet_source.max_column))
    return sheet_source


def get_col_index(sheet_src, col_title):
    num_col_max_src = sheet_src.max_column
    for col in range(1, num_col_max_src+1):
        cell_value_src = sheet_src.cell(1, col).value
        logging.debug('{}/{} : {}'.format(col, num_col_max_src, cell_value_src))
        if cell_value_src is not None:
            cell_value_src = cell_value_src.strip()
            cell_value_src = cell_value_src.replace(" ", "")
            if cell_value_src == col_title:
                return col


col_title_list = ['工单编号', '工单类型', '问题分类', '一级分类', '二级分类', '三级分类', '标签', '标题', '疏整促', '违建类型',
                  '疫情分类', '主要内容', '工单状态', '工单请求状态', '来电人', '来电号码/账号', '工单类型(直派/转派)', '处理结果',
                  '受理处理方式', '来电时间', '派单时间', '指派时间', '截止时间', '处理时限', '时限单位', '办结时间', '七有五性',
                  '事件分类', '日常子分类', '所属辖区', '问题点位', '执行人', '督办人', '参与部门', '承办部门', '接单部门',
                  '联系情况（街道）', '解决情况（街道）', '满意情况（街道）', '见面情况（街道）', '回访时间（街道）', '剔除挂账',
                  '指派人', '无效工单', '回访状态（区）', '回访时间（区）', '是否解决（区）', '是否满意（区）', '是否联系（区）']

col_title_list_key = {'工单编号': 'gongdan_id', '工单类型': 'gongdanleixing', '问题分类': 'wentifenlei',
                      '一级分类': 'yijifenlei', '二级分类': 'erjifenlei', '三级分类': 'sanjifenlei', '标签': 'biaoqian',
                      '标题': 'biaoti', '疏整促': 'cuzhengshu', '违建类型': 'weijianleixing', '疫情分类': 'yiqingfenlei',
                      '主要内容': 'zhuyaoneirong', '工单状态': 'gongdanzhuangtai', '工单请求状态': 'gongdanqingqiuzhuangtai',
                      '来电人': 'laidianren', '来电号码/账号': 'laidianhaoma', '工单类型(直派/转派)': 'gongdanleixing_zhipai',
                      '处理结果': 'chulijieguo', '受理处理方式': 'shoulichulifangshi', '来电时间': 'laidianshijian',
                      '派单时间': 'paidanshijian', '指派时间': 'zhipaishijian', '截止时间': 'jiezhishijian',
                      '处理时限': 'chulishixian', '时限单位': 'shixiandanwei', '办结时间': 'banjieshijian',
                      '七有五性': 'qiyouwuxing', '事件分类': 'shijianfenlei', '日常子分类': 'richangzifenpei',
                      '所属辖区': 'suoshuxiaqu', '问题点位': 'wentidianwei', '执行人': 'zhixingren', '督办人': 'dubanren',
                      '参与部门': 'canyubumen', '承办部门': 'chengbanbumen', '接单部门': 'jiedanbumen',
                      '联系情况（街道）': 'lianxiqingkuang_jiedao','解决情况（街道）': 'jiejueqingkuang_jiedao',
                      '满意情况（街道）': 'manyiqingkuang_jiedao', '见面情况（街道）': 'jianmianqingkuang_jiedao',
                      '是否属实（街道）': 'shifoushushi_jiedao', '回访时间（街道）': 'huifangshijian_jiedao',
                      '回访情况': 'huifangqingkuang', '剔除挂账': 'tichuguazhang', '指派人': 'zhipairen',
                      '无效工单': 'wuxiaogongdan', '考核类型（市中心）': 'kaoheleixing_shizhongxin',
                      '问题分类（市中心）': 'wentileixing_shizhongxin', '村/社区（市中心）': 'shequcun_shizhongxin',
                      '小区点位（市中心）': 'xiaoqudianwei_shizhongxin', '回访状态（区）': 'huifangzhaungtai_qu',
                      '回访时间（区）': 'huifangshijian_qu', '是否解决（区）': 'shifoujiejue_qu',
                      '是否满意（区）': 'shifoumanyi_qu', '是否联系（区）': 'shifoulianxi_qu'}

conn_obj_db = pymysql.connect(
    host='127.0.0.1',  # MySQL服务端的IP地址
    port=3306,  # MySQL默认PORT地址(端口号)
    user='root',  # 用户名
    password='password',  # 密码,也可以简写为passwd
    database='bidatabase',  # 库名称,也可以简写为db
    charset='utf8'  # 字符编码
)
cursor = conn_obj_db.cursor()


def do_execute_sql_select(db_cursor, select_gong_dan_id):
    # noinspection PyBroadException
    sql_select = "select paidanshijian from table_gongdan_new where gongdan_id='{}'".format(select_gong_dan_id)
    # logging.debug("sql_select:{}".format(sql_select))
    try:
        db_cursor.execute(sql_select)
        results = cursor.fetchone()
        return results[0]
    except Exception as e:
        logger.debug("没有查找到数据，sql_select:{}".format(sql_select))
        return ''


def do_update_shi_fou_shan_chu(db_cursor, update_gong_dan_id):
    # noinspection PyBroadException
    update_select = "UPDATE table_gongdan_new gd SET gd.shifoushanchu=2  WHERE gd.gongdan_id='{}'".format(update_gong_dan_id)
    # logging.debug("sql_select:{}".format(sql_select))
    try:
        db_cursor.execute(update_select)
        results = cursor.fetchone()
        return results[0]
    except Exception as e:
        logger.debug("更新数据失败，update_select:{}".format(update_select))
        return ''


def make_insert_sql(sheet_insert, row_num, col_title_list_insert):
    sql_insert = ''
    sql_insert_key = ''
    sql_insert_value = ''
    for col_insert in range(0, len(col_title_list)):
        col_insert_in_excel = col_title_list_insert[col_title_list[col_insert]]
        if col_insert_in_excel is not None:
            cell_value = sheet_insert.cell(row_num, col_insert_in_excel).value
            if cell_value is not None:
                if isinstance(cell_value, str):
                    cell_value = cell_value.replace('\\', '\\\\')
                    cell_value = cell_value.replace('\'', '\\\'')
                if col_title_list[col_insert] == '承办部门':
                    if col_insert != 0:
                        sql_insert_key = sql_insert_key + ','
                        sql_insert_value = sql_insert_value + ','
                    sql_insert_key = sql_insert_key + 'chengbanbumen,chengbanbumen_yuan'
                    sql_insert_value = sql_insert_value + '\'{}\',\'{}\''.format(cell_value, cell_value)
                else:
                    if col_insert != 0:
                        sql_insert_key = sql_insert_key + ','
                        sql_insert_value = sql_insert_value + ','
                    sql_insert_key = sql_insert_key + col_title_list_key[col_title_list[col_insert]]
                    sql_insert_value = sql_insert_value + '\'{}\''.format(cell_value)
                # print('{}:{}'.format(col_title_list_key[col_title_list[col_insert]], cell_value))
    pai_dan_shi_jian_excel_row = sheet_insert.cell(row_num, col_title_list_insert['派单时间']).value
    pai_dan_shi_jian_year = pai_dan_shi_jian_excel_row.timetuple().tm_year

    pai_dan_shi_jian_day = pai_dan_shi_jian_excel_row.timetuple().tm_mday
    if pai_dan_shi_jian_day <= 18:
        pai_dan_shi_jian_month = pai_dan_shi_jian_excel_row.timetuple().tm_mon
    else:
        pai_dan_shi_jian_month = pai_dan_shi_jian_excel_row.timetuple().tm_mon + 1
        if pai_dan_shi_jian_month > 12:
            pai_dan_shi_jian_month = 1
            pai_dan_shi_jian_year = pai_dan_shi_jian_year + 1

    sql_insert_key = sql_insert_key + ',paidanshijian_year'
    sql_insert_value = sql_insert_value + ',\'{}\''.format(pai_dan_shi_jian_year)

    sql_insert_key = sql_insert_key + ',paidanshijian_month'
    sql_insert_value = sql_insert_value + ',\'{}\''.format(pai_dan_shi_jian_month)

    pai_dan_shi_jian_year_month = '{}-{}'.format(pai_dan_shi_jian_year, pai_dan_shi_jian_month)
    sql_insert_key = sql_insert_key + ',paidanshijian_year_month'
    sql_insert_value = sql_insert_value + ',\'{}\''.format(pai_dan_shi_jian_year_month)

    cell_gong_dan_id_excel = sheet_insert.cell(row_num, col_title_list_insert['工单编号']).value
    pai_dan_shi_jian_sel = do_execute_sql_select(cursor, cell_gong_dan_id_excel)
    if pai_dan_shi_jian_sel != '':
        time_pai_dan_excel_time_stamp = int(time.mktime(pai_dan_shi_jian_excel_row.timetuple()))
        time_pai_dan_sql_time_stamp = int(time.mktime(pai_dan_shi_jian_sel.timetuple()))
        if time_pai_dan_excel_time_stamp >= time_pai_dan_sql_time_stamp:
            do_update_shi_fou_shan_chu(cursor, cell_gong_dan_id_excel)
        else:
            sql_insert_key = sql_insert_key + ',shifoushanchu'
            sql_insert_value = sql_insert_value + ',2'
    sql_insert = 'INSERT INTO table_gongdan_new(' + sql_insert_key + ')VALUES (' + sql_insert_value + ')'
    return sql_insert


def make_update_sql(sheet_update, row_num, col_title_list_update, gong_dan_id):
    sql_update = ''
    for col_update in range(1, len(col_title_list)):
        col_update_in_excel = col_title_list_update[col_title_list[col_update]]
        if col_update_in_excel is not None:
            cell_value_update = sheet_update.cell(row_num, col_update_in_excel).value
            if cell_value_update is not None:
                if col_update > 1:
                    sql_update = sql_update + ','
                sql_update_key = col_title_list_key[col_title_list[col_update]]
                sql_update = sql_update + "{}='{}'".format(sql_update_key, cell_value_update)
    sql_update = "UPDATE table_gongdan_new SET " + sql_update + " where gongdan_id='{}'".format(gong_dan_id)
    return sql_update


# 打开所有文件
def do_import_excel_data(file_year, num_pai_dan, num_gong_dan, num_all):
    for month in range(11, 13):
        col_title_list_index = {}
        file_path_cur = 'E:\\日常工作\\工单汇总\\{}\\'.format(file_year)
        file_name_month = get_excel_file_name(file_path_cur, month)
        logging.debug('文件:{}'.format(file_name_month))
        if os.path.exists(file_name_month):
            sheet_source_month = get_work_sheet(file_name_month)
            for indexCol in range(0, len(col_title_list)):
                col_index = get_col_index(sheet_source_month, col_title_list[indexCol])
                logging.debug('{}:{}'.format(col_title_list[indexCol], col_index))
                col_title_list_index[col_title_list[indexCol]] = col_index
            logging.debug('col_title_list_index：{}'.format(col_title_list_index))
            logging.debug('col_title_list_key：{}'.format(col_title_list_key))
            num_row_max = sheet_source_month.max_row
            num_col_max = sheet_source_month.max_column
            logging.debug('num_row_max：{}'.format(num_row_max))
            logging.debug('num_col_max：{}'.format(num_col_max))
            nCount = 0
            nCountPaiDan = 0
            nCountGongDan = 0
            for rowMonth in range(2, num_row_max+1):
                col_gong_dan_id = col_title_list_index['工单编号']
                col_pai_dan_shi_jian = col_title_list_index['派单时间']
                nCount = nCount + 1
                num_all = num_all + 1
                # logger.debug('{}----{}'.format(col_pai_dan_shi_jian, col_pai_dan_shi_jian))
                if col_pai_dan_shi_jian is None:
                    num_pai_dan = num_pai_dan + 1
                    nCountPaiDan = nCountPaiDan + 1
                    continue
                if col_gong_dan_id is None:
                    num_gong_dan = num_gong_dan + 1
                    nCountGongDan = nCountGongDan + 1
                    continue

                # cell_gong_dan_id = sheet_source_month.cell(rowMonth, col_gong_dan_id).value
                # if cell_gong_dan_id is None:
                #     continue
                # pai_dan_shi_jian_sel = do_execute_sql_select(cursor, cell_gong_dan_id)
                sql_make = ''
                # pai_dan_shi_jian_excel = sheet_source_month.cell(rowMonth, col_pai_dan_shi_jian).value
                # logging.debug('pai_dan_shi_jian_excel：{}'.format(pai_dan_shi_jian_excel))
                # if pai_dan_shi_jian_sel == '':
                sql_make = make_insert_sql(sheet_source_month, rowMonth, col_title_list_index)
                    # logger.debug('{}--{}- sql_make : {}'.format(rowMonth, cell_gong_dan_id, sql_make))
                # logging.debug('insert-{}:'.format(rowMonth))
                # else:
                #     # time_pai_dan_excel_array = time.strptime(pai_dan_shi_jian_excel, "%Y-%m-%d %H:%M:%S")
                #     time_pai_dan_excel_time_stamp = int(time.mktime(pai_dan_shi_jian_excel.timetuple()))
                #     logging.debug('pai_dan_shi_jian_sel : {}'.format(pai_dan_shi_jian_sel))
                #     # time_pai_dan_sql_array = time.strptime(pai_dan_shi_jian_sel, "%Y-%m-%d %H:%M:%S")
                #     time_pai_dan_sql_time_stamp = int(time.mktime(pai_dan_shi_jian_sel.timetuple()))
                #     if time_pai_dan_excel_time_stamp >= time_pai_dan_sql_time_stamp:
                #         sql_make = make_update_sql(sheet_source_month, rowMonth, col_title_list_index, cell_gong_dan_id)
                #         logging.debug('update-{}'.format(rowMonth))
                #     else:
                #         logging.debug('No-{}----{}'.format(time_pai_dan_sql_time_stamp, time_pai_dan_excel_time_stamp))
                #         logging.debug('No-{}----{}'.format(pai_dan_shi_jian_sel, col_pai_dan_shi_jian))
                try:
                    if sql_make != '':
                        logger.debug('{}/{}：{}-{} : {}'.format(rowMonth, num_row_max-1, file_year, month, sql_make))
                        cursor.execute(sql_make)
                        # 提交到数据库执行
                        conn_obj_db.commit()
                except Exception as e:
                    # 发生错误时回滚
                    logger.debug("发生错误时回滚 - sql_make : {}".format(sql_make))
                    # conn_obj_db.rollback()
            # logging.debug('{} 行'.format(nCount))
            logger.debug('工单总数：{}  派单时间为空:{} ---工单编号为空：{}'.format(num_all, nCountPaiDan, nCountGongDan))
        else:
            logger.debug('文件:{}---不存在'.format(file_name_month))


numPaiDanShiJianIsNull = 0
numGongDanIdNull = 0
numAll = 0

# do_import_excel_data(2020, numPaiDanShiJianIsNull, numGongDanIdNull, numAll)
# do_import_excel_data(2021, numPaiDanShiJianIsNull, numGongDanIdNull, numAll)
do_import_excel_data(2022, numPaiDanShiJianIsNull, numGongDanIdNull, numAll)

# 关闭不使用的游标对象
cursor.close()
# 关闭数据库连接
conn_obj_db.close()



